/**
 * <p>
 * 版权所有：中企动力
 * </p>
 * <p>
 * 未经本公司许可，不得以任何方式复制或使用本程序任何部分
 * </p>
 */
package com.ce.pms.report.dao.impl;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.hibernate.Query;

import com.ce.pms.common.util.SqlConfig;
import com.ce.pms.report.dao.IReportDao;
import com.ce.pms.report.queryvo.AlreadyChargeChargeTypeListQueryVO;
import com.ce.pms.report.queryvo.ChargeCollectQueryVO;
import com.ce.pms.report.queryvo.ClientArrearageBuildingListQueryVO;
import com.ce.pms.report.queryvo.ClientArrearageItemListQueryVO;
import com.ce.pms.report.queryvo.GrantBuildAndItemQueryVO;
import com.ce.pms.report.queryvo.GrantChargeItemListQueryVO;
import com.ce.pms.base.dao.impl.PmsBaseDaoImpl;
import com.ce.util.BlankUtil;
import com.ce.util.DateUtil;

/**
 *
 * @author <a href="mailto:i0o@live.cn">黄腾飞</a>
 * @Created 2008-11-10
 */

public class ReportDaoImpl extends PmsBaseDaoImpl implements IReportDao {

	private SqlConfig		sqlConfig	= null;

	private final String	SQLFILENAME	= "ReportSQL.xml";

	public ReportDaoImpl() {
		sqlConfig = new SqlConfig(SQLFILENAME);
	}

	/**
	 * 获取数据集合
	 *
	 * @param sqlItemName 配置文件中sql项的名称
	 * @param values 参数集合
	 * @return
	 * @author <a href="mailto:i0o@live.cn">黄腾飞</a>
	 * @created 2008-11-10
	 */
	public List<Object[]> getDataList(String sqlItemName, Object... values) {
		String sql = sqlConfig.getSql(sqlItemName);
		Query query = createSQLQuery(sql, values);
		return query.list();
	}

	/**
	 * 功能描述:使用命名参数 获取数据集合  <br>
	 * @param sqlItemName
	 * @param par  参数Map<String, Object>
	 * @return
	 * Create author:黄彦军 <br>
	 * Create on:2009-1-19  <br>
	 */
	public List<Object[]> getDataList(String sqlItemName,Map<String, Object> par){
		String sql = sqlConfig.getSql(sqlItemName);
		Query query = createNameSQLQuery(sql,par);
		return query.list();
	}

	/**
	 * 功能描述:查询 财务已收款收费方式明细表  <br>
	 * @param queryVO
	 * @return
	 * Create author:黄彦军 <br>
	 * Create on:2008-11-18  <br>
	 * Edit author:  <br>
	 * Edit on:    <br>
	 * Why:     <br>
	 */
	public List<Object[]> getAlrChargeTypeList(AlreadyChargeChargeTypeListQueryVO queryVO) {

		String period =DateUtil.convertDateToStr(queryVO.getComputeMonth(),"yyyyMM");
		StringBuffer sql = new StringBuffer();

		Map<String, Object> map = new HashMap<String, Object>();
		map.put("period", period);
		map.put("precinctId",queryVO.getPrecinct() );
		map.put("payMethod",BlankUtil.isBlank(queryVO.getPayType()) ? "%" : queryVO.getPayType());
		map.put("buildingId",BlankUtil.isBlank(queryVO.getBuildingId()) ? "%" : queryVO.getBuildingId());

		if("1".equals(queryVO.getStatType())){ //产权房
			String orderby_str = "ORDER BY STOREY_NUMBER, HOUSE_NUMBER";
			sql.append(sqlConfig.getSql("alreadyChargeChargeTypeList_pro").replaceAll("_ORDERBY_STR",orderby_str));
		}else if ("2".equals(queryVO.getStatType())){ //管理房
			sql.append(sqlConfig.getSql("alreadyChargeChargeTypeList_man"));
		}else{ //产权房 + 管理房
			sql.append(sqlConfig.getSql("alreadyChargeChargeTypeList_pro").replaceAll("_ORDERBY_STR",""));
			sql.append("  UNION ALL  ");
			sql.append(sqlConfig.getSql("alreadyChargeChargeTypeList_man"));
		}

		Query query = createNameSQLQuery(sql.toString(), map);
		return query.list();
	}

	/**
	 * 功能描述:查询 财务应收款分栋分项汇总表   <br>
	 * @param queryVO
	 * @return
	 * Create author:黄彦军 <br>
	 * Create on:2008-11-19  <br>
	 * Edit author:  <br>
	 * Edit on:    <br>
	 * Why:     <br>
	 */
	public List<Object[]> getGrantBuildAndItemList(GrantBuildAndItemQueryVO queryVO) {
		String condition = "";
		if ("1".equals(queryVO.getSubentry())){//本月
			condition = "=";
		}else if("2".equals(queryVO.getSubentry())){//往月
			condition = "<";
		}else{//本月　＋　往月
			condition = "<=";
		}
		String period = DateUtil.convertDateToStr(queryVO.getComputeMonth(), "yyyyMM"); //收费周期

		String sql = sqlConfig.getSql("grantBuildAndItem");
		sql = sql.replaceAll("_CONDITION", condition);

		Map<String, Object> map = new HashMap<String, Object>();
		map.put("period",period);
		map.put("precinct",queryVO.getPrecinct());

		Query query = createNameSQLQuery(sql.toString(), map);

		return query.list();
	}

	/**
	 * 功能描述: 查询 财务应收款分项明细表 <br>
	 * @param queryVO
	 * @return
	 * Create author:黄彦军 <br>
	 * Create on:2008-11-19  <br>
	 * Edit author:  <br>
	 * Edit on:    <br>
	 * Why:     <br>
	 */
	@SuppressWarnings("unchecked")
	public List<Object[]> getGrantChargeItemList(GrantChargeItemListQueryVO queryVO) {

		String period = DateUtil.convertDateToStr(queryVO.getComputeMonth(), "yyyyMM");

		String sql = sqlConfig.getSql("grantChargeItemList");

		Map<String, Object> map = new HashMap<String, Object>();
		map.put("subentry",queryVO.getSubentry());
		map.put("period", period);
		map.put("precinctId",queryVO.getPrecinct());
		map.put("buildingId", BlankUtil.isBlank(queryVO.getBuildingId()) ? '%' : queryVO.getBuildingId());

		Query query = createNameSQLQuery(sql,map);
		return query.list();
	}

	/**
	 * 功能描述: 查询 客户欠款分栋分项汇总表 <br>
	 * @param queryVO
	 * @return
	 * Create author:黄彦军 <br>
	 * Create on:2008-11-26  <br>
	 * Edit author:  <br>
	 * Edit on:    <br>
	 * Why:     <br>
	 */
	public List<Object[]> getArrearageBuildingList(ClientArrearageBuildingListQueryVO queryVO){
		String period = DateUtil.convertDateToStr(queryVO.getComputeMonth(), "yyyyMM");
		String precinct = queryVO.getPrecinct();
		String subentry = queryVO.getSubentry();
		String sql = sqlConfig.getSql("clientArrearageBuildingList");
		sql = sql.replaceAll("_PERIOD_V",period );
		sql = sql.replaceAll("_SUBENTRY", subentry);

		Query query = createSQLQuery(sql, new Object[]{precinct});

		return query.list();
	}


	/**
	 * 功能描述: 查询 客户欠款分项明细  <br>
	 * @param queryVO
	 * @return
	 * Create author:黄彦军 <br>
	 * Create on:2008-11-26  <br>
	 * Edit author:  <br>
	 * Edit on:    <br>
	 * Why:     <br>
	 */
	public List<Object[]> getArrearageItemList(ClientArrearageItemListQueryVO queryVO) {

		String precinct = queryVO.getPrecinct();
		String building = "";

		if(BlankUtil.isBlank(queryVO.getBuildingId())){
			building= "%";
		}else{
			building = queryVO.getBuildingId().toString();
		}

		String subentry = queryVO.getSubentry();
		String period =DateUtil.convertDateToStr(queryVO.getComputeMonth(), "yyyyMM");
		Integer monCount = queryVO.getMonCount();

		String sql = sqlConfig.getSql("clientArrearageItemList");
		sql = sql.replaceAll("_PERIOD_V", period); //替换 收费周期
		sql = sql.replaceAll("_SUBENTRY", subentry);  //替换 分项

		Object[] obj = null;
		if (BlankUtil.isBlank(monCount)){
			sql = sql.replaceAll("_DEBTMONTH_V", " ");
			obj = new Object[]{precinct,building};
		}else{ // 增加欠款月数查询条件
			sql = sql.replaceAll("_DEBTMONTH_V", "  HAVING DEBTMONTH >= ?");
			obj = new Object[]{precinct,building,monCount};
		}

		if ("1".equals(subentry)){
			sql = sql.replaceAll("_CONDITION", "=");
		}else if ("2".equals(subentry)){
			sql = sql.replaceAll("_CONDITION", "<");
		}else{
			sql = sql.replaceAll("_CONDITION", "<=");
		}
		Query query = createSQLQuery(sql, obj);
		return query.list();
	}

	/**
	 * 功能描述: 查询 收费汇总表 <br>
	 * @param queryVO
	 * @return
	 * Create author:黄彦军 <br>
	 * Create on:2009-1-19  <br>
	 */
	public List<Object[]> getChargeCollect(ChargeCollectQueryVO queryVO){

		String period = DateUtil.convertDateToStr(queryVO.getComputeMonth(), "yyyyMM");
		String subentry = queryVO.getSubentry();

		Map<String, Object> map = new HashMap<String, Object>();
		map.put("period",period);
		map.put("precinctId", queryVO.getPrecinct());
		map.put("buildingId", BlankUtil.isBlank(queryVO.getBuildingId()) ? "%" : queryVO.getBuildingId());

		String sql ="";
		if ("1".equals(subentry)){ //本月
			sql = sqlConfig.getSql("chargeCollect_Cur");
		}else if ("2".equals(subentry)){//往月
			sql = sqlConfig.getSql("chargeCollect_Bef");
		}else {//本月 + 往月
			sql = sqlConfig.getSql("chargeCollect_All");
		}

		Query query = createNameSQLQuery(sql, map);
		return query.list();
	}
}
